package com.aasa.siccosap.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.aasa.siccosap.bean.GarantiaBancaria;
import com.aasa.siccosap.bean.ParametrosGarantia;
import com.aasa.siccosap.bean.ParametrosGenerales;
import com.aasa.siccosap.util.Constantes;

public class GarantiaBancariaDAO extends ConsultaClienteBaseDAO{
	
	static Logger logger = Logger.getLogger("GarantiaBancariaDAO.class");
	public GarantiaBancariaDAO()	throws accesoDaoException {
		super();
	}
	
	public GarantiaBancariaDAO(DataSource dataSource) {
		super(dataSource);
	}
	
	
	public List<GarantiaBancaria> consultarGarantiasBancarias(ParametrosGenerales parametros) throws Exception{
        ResultSet rs = null;
        this.av_message = null;
        this.storedProcedure = "begin CONSULTA_GARANTIAS_PKG.P_GARANTIAS_BANCARIAS(?,?,?); end;";
        
        List<GarantiaBancaria> consultaGarantiasBancarias =new ArrayList<GarantiaBancaria>();
        
        try{
                this.conn = dataSource.getConnection();
                this.stmt = this.conn.prepareCall(storedProcedure);
                              
                configuraParametrosConsultaClienteGenerales(parametros);
                
                this.stmt.execute();

                //Se obtiene la respuesta error/exito del stored
                this.av_message = (String)stmt.getObject(1);                	
                    if(this.av_message.trim().toUpperCase().endsWith(Constantes.NO_ERRORS)){
                        //Se obtiene el objeto del cursor
                        rs = (ResultSet)stmt.getObject(2);
                        while(rs.next()){
                        	GarantiaBancaria garantia = creaRegistroGarantiaBancaria(rs);
                        	consultaGarantiasBancarias.add(garantia);
                        }
                }else{
                    return null;
                }

        }catch (Exception e){
                e.printStackTrace();
                return null;
        }finally{
        		close(this.conn, rs, stmt);
        }
        return(consultaGarantiasBancarias);
        
    }

	private GarantiaBancaria creaRegistroGarantiaBancaria(ResultSet rs) throws SQLException {
		GarantiaBancaria bean = new GarantiaBancaria();
		
		bean.setStrPb_banco(rs.getString("TI_BAN_COD_BCO"));
		bean.setObjPb_fechaVencimiento(rs.getDate("FEC_FIN"));
		bean.setStrPb_linea(rs.getString("TI_DES_LINEA"));
		bean.setStrPb_moneda(rs.getString("TI_DES_MONEDA"));
		bean.setDblPb_monto(rs.getDouble("IMP_APR_GBAN"));
		bean.setStrPb_numero(rs.getString("NUM_GBAN"));
		bean.setStrPb_tipoGarantia(rs.getString("TI_TIP_GBAN"));
		
		return bean;
	}
	
	private GarantiaBancaria creaRegistroGarantiaBancariaRenovada(ResultSet rs) throws SQLException {
		GarantiaBancaria bean = new GarantiaBancaria();		
		
		bean.setObjPb_fechaFin(rs.getDate("FEC_FIN"));
		bean.setObjPb_fechaIni(rs.getDate("FEC_INI"));
		bean.setStrPb_moneda(rs.getString("TI_DES_MONEDA"));
		bean.setDblPb_monto(rs.getDouble("IMP_APR_GBAN"));
		bean.setStrPb_numero(rs.getString("NUM_GBAN"));
		bean.setStrPb_tipoGarantia(rs.getString("TIP_GBAN"));
		
		return bean;
	}

	public List<GarantiaBancaria> consultarGarantiaBancariasRenovadas(
			ParametrosGarantia parametros) throws Exception{
		
		 ResultSet rs = null;
	        this.av_message = null;
	        this.storedProcedure = "begin CONSULTA_GARANTIAS_PKG.P_GARANTIAS_BANC_RENOV(?,?,?); end;";
		
	        List<GarantiaBancaria> consultaGarantiasBancarias =new ArrayList<GarantiaBancaria>();
	        
	        try{
	                this.conn = dataSource.getConnection();
	                this.stmt = this.conn.prepareCall(storedProcedure);
	                              
	                configuraParametrosConsultaClienteGenerales(parametros);
	                
	                this.stmt.execute();

	                //Se obtiene la respuesta error/exito del stored
	                this.av_message = (String)stmt.getObject(1);                	
	                    if(this.av_message.trim().toUpperCase().endsWith(Constantes.NO_ERRORS)){
	                        //Se obtiene el objeto del cursor
	                        rs = (ResultSet)stmt.getObject(2);
	                        while(rs.next()){
	                        	GarantiaBancaria garantia = creaRegistroGarantiaBancariaRenovada(rs);
	                        	consultaGarantiasBancarias.add(garantia);
	                        }
	                }else{
	                    return null;
	                }

	        }catch (Exception e){
	                e.printStackTrace();
	                return null;
	        }finally{
	        		close(this.conn, rs, stmt);
	        }
	        return(consultaGarantiasBancarias);
		
	}
}
